
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using AnovoAndes.Framework.Utilidades;
using NovaSystem.EntidadesNegocio;

namespace NovaSystem.AccesoDatos
{
	public class facturaventaDAO 
	{
			
		#region [Variables Publicas]
        List<string> listaEsquema = new List<string>();
		System.Configuration.AppSettingsReader read = new System.Configuration.AppSettingsReader();
        #endregion
		
		#region Constructores
		public facturaventaDAO()
		{
		}
		#endregion
		
		#region Metodos Privados
		
		 private List<string> LlenarEsquema(IDataReader dr)
        {
            DataTable schemaTable;
            schemaTable = dr.GetSchemaTable();
            listaEsquema = new List<string>();
            const int columnaNombre = 0;
            //Para cada campo de la tabla...
            foreach (DataRow myField in schemaTable.Rows)
            {
                //Agrega nombre del campo a la lista generica.
                listaEsquema.Add(myField[columnaNombre].ToString());
            }
            return listaEsquema;

        }
		
		private facturaventa Crear(IDataReader dr)
		{
			facturaventa vObj = new facturaventa();
			try
			{
				listaEsquema.Sort();
							if (listaEsquema.BinarySearch("idFactura",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["idFactura"] is System.DBNull))
					{
						vObj.idFactura= (int)dr["idFactura"];
					}
				}
				if (listaEsquema.BinarySearch("idCliente",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["idCliente"] is System.DBNull))
					{
						vObj.idCliente= (int)dr["idCliente"];
					}
				}
				if (listaEsquema.BinarySearch("numeroFactura",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["numeroFactura"] is System.DBNull))
					{
						vObj.numeroFactura= (int)dr["numeroFactura"];
					}
				}
				if (listaEsquema.BinarySearch("fechaFactura",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["fechaFactura"] is System.DBNull))
					{
						vObj.fechaFactura= (DateTime)dr["fechaFactura"];
					}
				}
				if (listaEsquema.BinarySearch("afectaExenta",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["afectaExenta"] is System.DBNull))
					{
						vObj.afectaExenta= (string)dr["afectaExenta"];
					}
				}
				if (listaEsquema.BinarySearch("direccion",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["direccion"] is System.DBNull))
					{
						vObj.direccion= (string)dr["direccion"];
					}
				}
				if (listaEsquema.BinarySearch("conceptoPais",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["conceptoPais"] is System.DBNull))
					{
						vObj.conceptoPais= (string)dr["conceptoPais"];
					}
				}
				if (listaEsquema.BinarySearch("codigoPais",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["codigoPais"] is System.DBNull))
					{
						vObj.codigoPais= (string)dr["codigoPais"];
					}
				}
				if (listaEsquema.BinarySearch("subTotal",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["subTotal"] is System.DBNull))
					{
						vObj.subTotal= (decimal)dr["subTotal"];
					}
				}
				if (listaEsquema.BinarySearch("impuesto",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["impuesto"] is System.DBNull))
					{
						vObj.impuesto= (decimal)dr["impuesto"];
					}
				}
				if (listaEsquema.BinarySearch("totalImpuesto",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["totalImpuesto"] is System.DBNull))
					{
						vObj.totalImpuesto= (decimal)dr["totalImpuesto"];
					}
				}
				if (listaEsquema.BinarySearch("total",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["total"] is System.DBNull))
					{
						vObj.total= (decimal)dr["total"];
					}
				}
				if (listaEsquema.BinarySearch("descuento",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["descuento"] is System.DBNull))
					{
						vObj.descuento= (decimal)dr["descuento"];
					}
				}
				if (listaEsquema.BinarySearch("observacion",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["observacion"] is System.DBNull))
					{
						vObj.observacion= (string)dr["observacion"];
					}
				}
				if (listaEsquema.BinarySearch("fechaRegistro",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["fechaRegistro"] is System.DBNull))
					{
						vObj.fechaRegistro= (DateTime)dr["fechaRegistro"];
					}
				}
				if (listaEsquema.BinarySearch("usuario",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["usuario"] is System.DBNull))
					{
						vObj.usuario= (string)dr["usuario"];
					}
				}
                if (listaEsquema.BinarySearch("ordenCompraRef", StringComparer.OrdinalIgnoreCase) > -1)
                {
                    if (!(dr["ordenCompraRef"] is System.DBNull))
                    {
                        vObj.OrdenCompraRef = (string)dr["ordenCompraRef"];
                    }
                }


				return vObj;
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message);
			}
		}
		
		private List<facturaventa> CrearDTOs(IDataReader dr) 
		{
			List<facturaventa> listaDTO = new List<facturaventa>();
			if 	(dr != null)
			{
				listaEsquema = LlenarEsquema(dr);// Validadores.LlenarEsquema(dr);
				
				while (dr.Read())
				{
					listaDTO.Add(Crear(dr));
				}
				dr.Close();
			}
			return listaDTO;
		}
		
		#endregion
		
		#region Metodos Publicos
		public List<facturaventa> Listar()
		{
			MySqlConnection conn = new MySqlConnection();
			List<facturaventa> lista = new List<facturaventa>();
			try
			{
				string connString = read.GetValue("Database",typeof(string)).ToString();
				conn.ConnectionString = connString;
				conn.Open();
				MySqlCommand objDbCommand = new MySqlCommand();
				objDbCommand.Connection = conn;
				objDbCommand.CommandType = CommandType.StoredProcedure;
				objDbCommand.CommandText = "usp_s_facturaventa";
				
				using (IDataReader objDataReader = objDbCommand.ExecuteReader())
				{
					lista = CrearDTOs(objDataReader);
				}
				CerrarConn(conn);
				return lista;
			}
			catch(Exception ex)
			{
				CerrarConn(conn);
				throw new Exception(ex.Message);
			}
		}
		
		
		private void CerrarConn(MySqlConnection conn)
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
            }
        }
		
		public string Insertar( facturaventa dto)
		{
			MySqlConnection conn = new MySqlConnection();
			try
			{
				string connString = read.GetValue("Database",typeof(string)).ToString();
				conn.ConnectionString = connString;
				conn.Open();
				MySqlCommand objDbCommand = new MySqlCommand();
				objDbCommand.Connection = conn;
				objDbCommand.CommandType = CommandType.StoredProcedure;
				objDbCommand.CommandText = "usp_I_facturaventa";
				objDbCommand.Parameters.AddWithValue("_idFactura",AssertNull.getValue(dto.idFactura));
                objDbCommand.Parameters.AddWithValue("_ordenCompraRef", AssertNull.getValue(dto.OrdenCompraRef));
				objDbCommand.Parameters.AddWithValue("_idCliente",AssertNull.getValue(dto.idCliente));
				objDbCommand.Parameters.AddWithValue("_numeroFactura",AssertNull.getValue(dto.numeroFactura));
				objDbCommand.Parameters.AddWithValue("_fechaFactura",AssertNull.getValue(dto.fechaFactura));
				objDbCommand.Parameters.AddWithValue("_afectaExenta",AssertNull.getValue(dto.afectaExenta));
				objDbCommand.Parameters.AddWithValue("_direccion",AssertNull.getValue(dto.direccion));
				objDbCommand.Parameters.AddWithValue("_conceptoPais",AssertNull.getValue(dto.conceptoPais));
				objDbCommand.Parameters.AddWithValue("_codigoPais",AssertNull.getValue(dto.codigoPais));
				objDbCommand.Parameters.AddWithValue("_subTotal",AssertNull.getValue(dto.subTotal));
				objDbCommand.Parameters.AddWithValue("_impuesto",AssertNull.getValue(dto.impuesto));
				objDbCommand.Parameters.AddWithValue("_totalImpuesto",AssertNull.getValue(dto.totalImpuesto));
				objDbCommand.Parameters.AddWithValue("_total",AssertNull.getValue(dto.total));
				objDbCommand.Parameters.AddWithValue("_descuento",AssertNull.getValue(dto.descuento));
				objDbCommand.Parameters.AddWithValue("_observacion",AssertNull.getValue(dto.observacion));
				objDbCommand.Parameters.AddWithValue("_fechaRegistro",AssertNull.getValue(dto.fechaRegistro));
				objDbCommand.Parameters.AddWithValue("_usuario",AssertNull.getValue(dto.usuario));
                objDbCommand.Parameters["_idFactura"].Direction = ParameterDirection.Output;
				objDbCommand.ExecuteNonQuery();
				CerrarConn(conn);
				return "";
			}
			catch(Exception ex)
			{
				CerrarConn(conn);
				throw new Exception(ex.Message);
			}
		}
		
		public void Eliminar(facturaventa dto)
		{
			MySqlConnection conn = new MySqlConnection();
			try
			{
				string connString = read.GetValue("Database",typeof(string)).ToString();
				conn.ConnectionString = connString;
				conn.Open();
				MySqlCommand objDbCommand = new MySqlCommand();
				objDbCommand.Connection = conn;
				objDbCommand.CommandType = CommandType.StoredProcedure;
				objDbCommand.CommandText = "usp_D_facturaventa";
								objDbCommand.Parameters.AddWithValue("_idFactura",AssertNull.getValue(dto.idFactura));

				objDbCommand.ExecuteNonQuery();
				CerrarConn(conn);
			}
			catch(Exception ex)
			{
				CerrarConn(conn);
				throw new Exception(ex.Message);
			}
		}
		
		#endregion
		
	}
}
